﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Healthy
{
    public partial class frmosm : Form
    {
        public frmosm()
        {
            InitializeComponent();
        }
        int num;
        private void frmosm_Load(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
          
        }
        private void showdata2()
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sql = "SELECT * FROM osm ";
            SqlCommand cmd = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();

            //Conn.Open();
            odbcDA.Fill(ds);
            //cmd.ExecuteReader();

            this.dvgosm.DataSource = ds.Tables[0];

            DataGridViewCellStyle cs = new DataGridViewCellStyle();
            cs.Font = new Font("MS Sans Serif", 14, FontStyle.Regular);

            this.dvgosm.ColumnHeadersDefaultCellStyle = cs;
            this.dvgosm.Columns[0].HeaderText = "รหัสอสม.";
            this.dvgosm.Columns[1].HeaderText = "ชื่อ";
            this.dvgosm.Columns[2].HeaderText = "ตำแหน่ง";
            this.dvgosm.Columns[3].HeaderText = "ระดับการศึกษา";
            this.dvgosm.Columns[4].HeaderText = "เบอร์โทร";
            this.dvgosm.Columns[5].HeaderText = "อีเมลล์";
            this.dvgosm.Columns[6].HeaderText = "ที่อยู่";

            this.dvgosm.Columns[0].Width = 100;
            this.dvgosm.Columns[1].Width = 200;
            this.dvgosm.Columns[2].Width = 150;
            this.dvgosm.Columns[3].Width = 150;
            this.dvgosm.Columns[4].Width = 150;
            this.dvgosm.Columns[5].Width = 150;
            this.dvgosm.Columns[6].Width = 150;
            
        }
        private void Format2()
        {
            DataGridViewCellStyle cs = new DataGridViewCellStyle();
            cs.Font = new Font("MS Sans Serif", 14, FontStyle.Regular);

            this.dvgosm.ColumnHeadersDefaultCellStyle = cs;
            this.dvgosm.Columns[0].HeaderText = "รหัสอสม.";
            this.dvgosm.Columns[1].HeaderText = "ชื่อ";
            this.dvgosm.Columns[2].HeaderText = "ตำแหน่ง";
            this.dvgosm.Columns[3].HeaderText = "ระดับการศึกษา";
            this.dvgosm.Columns[4].HeaderText = "เบอร์โทร";
            this.dvgosm.Columns[5].HeaderText = "อีเมลล์";
            this.dvgosm.Columns[6].HeaderText = "ที่อยู่";

            this.dvgosm.Columns[0].Width = 100;
            this.dvgosm.Columns[1].Width = 200;
            this.dvgosm.Columns[2].Width = 150;
            this.dvgosm.Columns[3].Width = 150;
            this.dvgosm.Columns[4].Width = 150;
            this.dvgosm.Columns[5].Width = 150;
            this.dvgosm.Columns[6].Width = 150;
            
            dvgosm.Rows[dvgosm.Rows.Count - 1].Height = 0;

        }
        private void ShowOsm()
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sql = "select * from osm Where OSMID = '" + osmid.Text + "'";
            SqlCommand cmd1 = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
            DataSet ds = new DataSet();

            odbcDA.Fill(ds, "osm");
            num = ds.Tables["osm"].Rows.Count;

            DataGridViewCellStyle cs = new DataGridViewCellStyle();
            cs.Font = new Font("MS Sans Serif", 14, FontStyle.Regular);
            this.dvgosm.ColumnHeadersDefaultCellStyle = cs;
            this.dvgosm.Columns[0].HeaderText = "รหัสอสม.";
            this.dvgosm.Columns[1].HeaderText = "ชื่อ";
            this.dvgosm.Columns[2].HeaderText = "ตำแหน่ง";
            this.dvgosm.Columns[3].HeaderText = "ระดับการศึกษา";
            this.dvgosm.Columns[4].HeaderText = "เบอร์โทร";
            this.dvgosm.Columns[5].HeaderText = "อีเมลล์";
            this.dvgosm.Columns[6].HeaderText = "ที่อยู่";

            this.dvgosm.Columns[0].Width = 100;
            this.dvgosm.Columns[1].Width = 200;
            this.dvgosm.Columns[2].Width = 150;
            this.dvgosm.Columns[3].Width = 150;
            this.dvgosm.Columns[4].Width = 150;
            this.dvgosm.Columns[5].Width = 150;
            this.dvgosm.Columns[6].Width = 150;
            

         
        }

        private void insert_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();


            string query = "insert into osm values('" + osmid.Text + "','" + osmName.Text + "','" + status.Text + "','" + edu.Text + "','" + maskedTextBox1.Text + "','" + textBox4.Text + "','" + textBox5.Text + "')";

            SqlCommand cmd7 = new SqlCommand(query, Conn);
            cmd7.ExecuteNonQuery();

            string sql = "select * from osm Where OSMID= '" + osmid.Text + "' ";
            SqlCommand cmd1 = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
            DataSet ds = new DataSet();
            odbcDA.Fill(ds);
           
            this.dvgosm.DataSource = ds.Tables[0];

            DataGridViewCellStyle cs = new DataGridViewCellStyle();
            cs.Font = new Font("MS Sans Serif", 14, FontStyle.Regular);
            this.dvgosm.ColumnHeadersDefaultCellStyle = cs;
            this.dvgosm.Columns[0].HeaderText = "รหัสอสม.";
            this.dvgosm.Columns[1].HeaderText = "ชื่อ";
            this.dvgosm.Columns[2].HeaderText = "ตำแหน่ง";
            this.dvgosm.Columns[3].HeaderText = "ระดับการศึกษา";
            this.dvgosm.Columns[4].HeaderText = "เบอร์โทร";
            this.dvgosm.Columns[5].HeaderText = "อีเมลล์";
            this.dvgosm.Columns[6].HeaderText = "ที่อยู่";

            this.dvgosm.Columns[0].Width = 100;
            this.dvgosm.Columns[1].Width = 200;
            this.dvgosm.Columns[2].Width = 150;
            this.dvgosm.Columns[3].Width = 150;
            this.dvgosm.Columns[4].Width = 150;
            this.dvgosm.Columns[5].Width = 150;
            this.dvgosm.Columns[6].Width = 150;
            

        }

        private void delete_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string query = "delete from osm where OSMID='" + osmid.Text + "'";
            SqlCommand cmd = new SqlCommand(query, Conn);
            cmd.ExecuteNonQuery();

            string sql = "SELECT * FROM osm ";
            SqlCommand cmd1 = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
            DataSet ds = new DataSet();

            //Conn.Open();
            odbcDA.Fill(ds);
            //cmd.ExecuteReader();
            this.dvgosm.DataSource = ds.Tables[0];

            DataGridViewCellStyle cs = new DataGridViewCellStyle();
            cs.Font = new Font("MS Sans Serif", 14, FontStyle.Regular);


            this.dvgosm.ColumnHeadersDefaultCellStyle = cs;
            this.dvgosm.Columns[0].HeaderText = "รหัสอสม.";
            this.dvgosm.Columns[1].HeaderText = "ชื่อ";
            this.dvgosm.Columns[2].HeaderText = "ตำแหน่ง";
            this.dvgosm.Columns[3].HeaderText = "ระดับการศึกษา";
            this.dvgosm.Columns[4].HeaderText = "เบอร์โทร";
            this.dvgosm.Columns[5].HeaderText = "อีเมลล์";
            this.dvgosm.Columns[6].HeaderText = "ที่อยู่";

            this.dvgosm.Columns[0].Width = 100;
            this.dvgosm.Columns[1].Width = 200;
            this.dvgosm.Columns[2].Width = 150;
            this.dvgosm.Columns[3].Width = 150;
            this.dvgosm.Columns[4].Width = 150;
            this.dvgosm.Columns[5].Width = 150;
            this.dvgosm.Columns[6].Width = 150;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            if (osmid.Text == "" || osmName.Text == "" || status.Text == "" || edu.Text == "" || maskedTextBox1.Text == "" || textBox4.Text == "" || textBox5.Text == "")
            {
                MessageBox.Show("กรุณาป้อนข้อมูลให้ครบ!!!", "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Information);
                osmid.Focus();
                osmid.SelectAll();
                return;
            }
            //string sqlupdate = "update osm set OSMID='" + osmid.Text + "',MEDCNM='" + osmName.Text + "',DIST='" + osmlastnam.Text +
            string sqlupdate = "update osm set OSMID='" + osmid.Text + "',OSMNAM='" + osmName.Text + "',OSMPOS='" + status.Text + "',EDULAVEL='" + edu.Text + "',TEL='" + maskedTextBox1.Text + "',EMAIL='" + textBox4.Text + "',ADDRESS2='" + textBox5.Text + "' where OSMID='" + osmid.Text + "'";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sqlupdate;
            cmd.Connection = Conn;
            cmd.ExecuteNonQuery();
            ShowOsm();

            osmid.Text = "";
            osmName.Text = "";
            status.Text = "";
            edu.Text = "";
            maskedTextBox1.Text = "" ;
            textBox4.Text = "" ;
            textBox5.Text = "";
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sql = "SELECT * FROM osm ";
            SqlCommand cmd = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();

            //Conn.Open();
            odbcDA.Fill(ds);
            //cmd.ExecuteReader();
            this.dvgosm.DataSource = ds.Tables[0];

            DataGridViewCellStyle cs = new DataGridViewCellStyle();
            cs.Font = new Font("MS Sans Serif", 14, FontStyle.Regular);


            this.dvgosm.ColumnHeadersDefaultCellStyle = cs;
            this.dvgosm.Columns[0].HeaderText = "รหัสอสม.";
            this.dvgosm.Columns[1].HeaderText = "ชื่อ";
            this.dvgosm.Columns[2].HeaderText = "ตำแหน่ง";
            this.dvgosm.Columns[3].HeaderText = "ระดับการศึกษา";
            this.dvgosm.Columns[4].HeaderText = "เบอร์โทร";
            this.dvgosm.Columns[5].HeaderText = "อีเมลล์";
            this.dvgosm.Columns[6].HeaderText = "ที่อยู่";

            this.dvgosm.Columns[0].Width = 100;
            this.dvgosm.Columns[1].Width = 200;
            this.dvgosm.Columns[2].Width = 150;
            this.dvgosm.Columns[3].Width = 150;
            this.dvgosm.Columns[4].Width = 150;
            this.dvgosm.Columns[5].Width = 150;
            this.dvgosm.Columns[6].Width = 150;
        }

        private void close_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void dvgosm_CellMouseUp(object sender, DataGridViewCellMouseEventArgs e)
        {

            osmid.Text = Convert.ToString(dvgosm.Rows[e.RowIndex].Cells[0].Value);
            osmName.Text = Convert.ToString(dvgosm.Rows[e.RowIndex].Cells[1].Value);
            status.Text = Convert.ToString(dvgosm.Rows[e.RowIndex].Cells[2].Value);
            edu.Text = Convert.ToString(dvgosm.Rows[e.RowIndex].Cells[3].Value);
            maskedTextBox1.Text = Convert.ToString(dvgosm.Rows[e.RowIndex].Cells[4].Value);
            textBox4.Text = Convert.ToString(dvgosm.Rows[e.RowIndex].Cells[5].Value);
            textBox5.Text = Convert.ToString(dvgosm.Rows[e.RowIndex].Cells[6].Value);
            
        }
    }
}
